insert
    overwrite
    table jms_tmp.tmp_scan_bagging_trace
select package_code,
       max(real_road)      real_road,
       max(real_road_name) real_road_name,
       max(start_time)     start_time,
       max(end_time)       end_time
from (select waybill_no                                                                   package_code,
             regexp_replace(concat_ws('-', sort_array(
                     collect_list(CONCAT(scan_time, ' ', scan_site_code))
                                  over (partition by waybill_no))),
                            '[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2} ', '') real_road,
             regexp_replace(concat_ws('-', sort_array(
                     collect_list(CONCAT(scan_time, ' ', scan_site))
                                  over (partition by waybill_no))),
                            '[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2} ',
                            '')                                                           real_road_name,
             first_time                                                                   start_time,
             last_time                                                                    end_time
      from (select *,
                   lag(scan_site_code, 1, '')
                       over (partition by waybill_no order by scan_time desc,input_time desc ) lag_site_code,
                   first_value(scan_time) over (partition by waybill_no order by scan_time)    first_time,
                   first_value(scan_time)
                               over (partition by waybill_no order by scan_time desc)          last_time
            from (select *,
                         min(if(scan_type_num = 4, scan_time, '2099-12-31 23:59:59'))
                             over (partition by waybill_no ) de_time
                  from (select waybill_no,
                               scan_type,
                               scan_site_code,
                               scan_site,
                               scan_time,
                               input_time,
                               1 scan_type_num
                        from jms_dwd.dwd_tab_barscan_bagging_base_dt
                        where dt between date_add(date('{{ execution_date | cst_ds }}'), -45 + 1) and '{{ execution_date | cst_ds }}'
                          and waybill_no like 'B%'
                        union all
                        select waybill_no,
                               scan_type,
                               scan_site_code,
                               input_site scan_site,
                               scan_time,
                               input_time,
                               2          scan_type_num
                        from jms_dwd.dwd_tab_barscan_send_base_dt
                        where dt between date_add(date('{{ execution_date | cst_ds }}'), -45 + 1) and '{{ execution_date | cst_ds }}'
                          and waybill_no like 'B%'
                        union all
                        select waybill_no,
                               scan_type,
                               scan_site_code,
                               scan_site scan_site,
                               scan_time,
                               input_time,
                               3         scan_type_num
                        from jms_dwd.dwd_tab_barscan_arrival_base_dt
                        where dt between date_add(date('{{ execution_date | cst_ds }}'), -45 + 1) and '{{ execution_date | cst_ds }}'
                          and waybill_no like 'B%'
                        union all
                        select waybill_no,
                               scan_type,
                               scan_site_code,
                               scan_site,
                               scan_time,
                               input_time,
                               4 scan_type_num
                        from jms_dwd.dwd_tab_barscan_other_base_dt
                        where dt between date_add(date('{{ execution_date | cst_ds }}'), -45 + 1) and '{{ execution_date | cst_ds }}'
                          and waybill_no like 'B%'
                          and scan_type = '拆包扫描') detail) lag
            where scan_time <= de_time) lead
      where lag_site_code != scan_site_code) bag_trace_detail
group by package_code
    distribute by abs(hash(package_code)) % 150;


with refund as (select waybill_no,
                       apply_type_code,
                       apply_type_name,
                       examine_network_code,
                       examine_time apply_time
                from jms_dwd.dwd_tab_reback_transfer_express_base_dt
                where dt >= date_add(date('{{ execution_date | cst_ds }}'), -30 + 1)
                  and dt <= '{{ execution_date | cst_ds }}'
                  and STATUS = '2' --面单是否已打印 1是 0否
                group by waybill_no, apply_type_code, apply_type_name, examine_time, examine_network_code),
     refund_dup as (select *
                    from (select waybill_no,
                                 apply_type_code,
                                 apply_type_name,
                                 row_number() over (partition by waybill_no order by apply_time desc) as rnk
                          from refund) detail
                    where rnk = 1
                    distribute by abs(hash(waybill_no))%300
                    ),
     refund_intercept as (select refund.waybill_no, nvl(intercept_time, apply_time) apply_time
                          from refund
                                   left join (SELECT min(intercept_time)       intercept_time
                                                   , waybill_no
                                                   , intercept_network_code as intercept_site_code
                                              from jms_dwd.dwd_yl_oms_interceptorpiece_base
                                              where to_date(intercept_time) >=
                                                    date_add(date('{{ execution_date | cst_ds }}'), -30 + 1)
                                                and to_date(intercept_time) <= '{{ execution_date | cst_ds }}'
                                              group by waybill_no, intercept_network_code) intercept
                                             on refund.waybill_no = intercept.waybill_no
                                                 and intercept.intercept_site_code = refund.examine_network_code),
     return_order as (select a.waybill_id,
                             a.terminal_dispatch_code     return_terminal_dispatch_code,
                             a.new_terminal_dispatch_code return_new_terminal_dispatch_code,
                             b.apply_time                 return_apply_time
                      from jms_dwd.dwd_yl_oms_order_return_dt a
                               left join refund_intercept b on a.waybill_id = b.waybill_no
                      where a.dt >= date_add(date('{{ execution_date | cst_ds }}'), -15 + 1)
                        and a.dt <= '{{ execution_date | cst_ds }}'
                        distribute by abs(hash(waybill_id))%300
                        ),
     transfer as (select a.waybill_id,
                         a.terminal_dispatch_code     transfer_terminal_dispatch_code,
                         a.new_terminal_dispatch_code transfer_new_terminal_dispatch_code,
                         b.apply_time                 transfer_apply_time
                  from jms_dwd.dwd_yl_oms_order_transfer_dt a
                           left join refund_intercept b on a.waybill_id = b.waybill_no
                  where a.dt >= date_add(date('{{ execution_date | cst_ds }}'), -15 + 1)
                    and a.dt <= '{{ execution_date | cst_ds }}'
                   distribute by abs(hash(waybill_id))%300
                    ),
     sign_detail as (select /*+ BROADCAST(transfer) */
                            waybill_no,
                            send_station_number,
                            send_station_network_code,
                            send_station_network_type,
                            next_station_network_code,
                            next_station_network_type,
                            nvl(split(terminal_dispatch_code_latest,',')[0],first_code) first_code,
                            nvl(split(terminal_dispatch_code_latest,',')[1],second_code) second_code,
                            nvl(split(terminal_dispatch_code_latest,',')[2],third_code) third_code,
                            send_station_send_next_network_code,
                            send_station_bagging_packagecode                                   package_code,
                            if(send_station_unbagging_packagecode = '', null,
                               send_station_unbagging_packagecode)                             send_station_unbagging_packagecode,
                            if(send_station_send_packagecode = '', null,
                               send_station_send_packagecode)                                  send_station_send_packagecode,
                            if(send_station_arrival_packagecode = '', null,
                               send_station_arrival_packagecode)                               send_station_arrival_packagecode,
                            case
                                when next_station_bagging_packagecode is not null and
                                     (if(send_station_send_packagecode = '', null,
                                         send_station_send_packagecode) is not null or
                                      if(send_station_arrival_packagecode = '', null,
                                         send_station_arrival_packagecode) is not null)
                                    then next_station_network_code
                                else null end                                                  next_code,
                            case
                                when next_station_bagging_packagecode is not null and
                                     (if(send_station_send_packagecode = '', null,
                                         send_station_send_packagecode) is not null or
                                      if(send_station_arrival_packagecode = '', null,
                                         send_station_arrival_packagecode) is not null)
                                    then next_station_number
                                else null end                                                  next_code_number,
                            send_station_bagging_lastest_scantime,
                            send_station_unbagging_scantime,
                            send_station_send_later_time,
                            send_station_arrival_early_time,
                            nvl(send_station_send_later_time, send_station_arrival_early_time) scan_time,
                            case
                                when if(send_station_send_next_network_code is null, next_station_network_type,
                                        send_station_send_next_network_type) in
                                     ('3', '4') and send_station_network_type = '2' and
                                     send_station_bagging_packagecode is not null then 1
                                else 0 end                                                     is_start,
                            dt,
                            return_order.return_apply_time                                     return_apply_time,
                            return_order.return_terminal_dispatch_code                         return_sys_code_old,
                            return_order.return_new_terminal_dispatch_code                     return_sys_code_new,
                            split(return_order.return_terminal_dispatch_code, ',')             return_sys_code_old_array,
                            split(return_order.return_new_terminal_dispatch_code, ',')         return_sys_code_new_array,
                            transfer.transfer_apply_time                                       transfer_apply_time,
                            transfer.transfer_terminal_dispatch_code                           transfer_sys_code_old,
                            transfer.transfer_new_terminal_dispatch_code                       transfer_sys_code_new,
                            split(transfer.transfer_terminal_dispatch_code, ',')               transfer_sys_code_old_array,
                            split(transfer.transfer_new_terminal_dispatch_code, ',')           transfer_sys_code_new_array
                     from jms_dwd.dwd_wide_sign_trace_waybill_step_dt a
                              left join return_order on return_order.waybill_id = a.waybill_no
                              left join transfer on transfer.waybill_id = a.waybill_no
                     where dt >= date_add(date('{{ execution_date | cst_ds }}'), -1 + 1)
                       and dt <= '{{ execution_date | cst_ds }}'
                       and coalesce(sign_early_scantime, outstore_early_scantime) >=
                           coalesce(send_station_send_later_time, send_station_arrival_early_time,
                                    send_station_bagging_lastest_scantime, send_station_unbagging_scantime)
                       AND is_sign = 1
                       )
insert
overwrite
table
jms_tmp.tmp_waybill_bagging_info
select * from sign_detail   distribute by abs(hash(concat(waybill_no,send_station_number)))%150 ;




with refund as (select waybill_no,
                       apply_type_code,
                       apply_type_name,
                       examine_network_code,
                       examine_time apply_time
                from jms_dwd.dwd_tab_reback_transfer_express_base_dt
                where dt >= date_add(date('{{ execution_date | cst_ds }}'), -30 + 1)
                  and dt <= '{{ execution_date | cst_ds }}'
                  and STATUS = '2' --面单是否已打印 1是 0否
                group by waybill_no, apply_type_code, apply_type_name, examine_time, examine_network_code),
     refund_dup as (
                    select *
                    from (
                    from (select waybill_no,
                                 apply_type_code,
                                 apply_type_name,
                                 row_number() over (partition by waybill_no order by apply_time desc) as rnk
                          from refund) detail
                    where rnk = 1) detail
                    ),
     refund_intercept as (select refund.waybill_no, nvl(intercept_time, apply_time) apply_time
                          from refund
                                   left join (SELECT min(intercept_time)       intercept_time
                                                   , waybill_no
                                                   , intercept_network_code as intercept_site_code
                                              from jms_dwd.dwd_yl_oms_interceptorpiece_base
                                              where to_date(intercept_time) >=
                                                    date_add(date('{{ execution_date | cst_ds }}'), -30 + 1)
                                                and to_date(intercept_time) <= '{{ execution_date | cst_ds }}'
                                              group by waybill_no, intercept_network_code) intercept
                                             on refund.waybill_no = intercept.waybill_no
                                                 and intercept.intercept_site_code = refund.examine_network_code),
     sign_detail as (select * from  jms_tmp.tmp_waybill_bagging_info)
        ,
     tab_bag_detail as (select waybill_no,
                               scan_type,
                               scan_site,
                               scan_site_code,
                               scan_user,
                               scan_user_code,
                               source,
                               scan_time,
                               input_time,
                               package_code,
                               good_type, ----物品类型1是普通类型 2是文件类型
                               row_number() over (partition by waybill_no order by scan_time desc ) rnk,
                               row_number() over (partition by waybill_no,package_code order by scan_time desc ) pack_rnk
                        from jms_dwd.dwd_tab_barscan_bagging_base_dt
                        where dt >= date_add(date('{{ execution_date | cst_ds }}'), -45 + 1)
                          and dt <= '{{ execution_date | cst_ds }}'
                          ),
     tab_bag as (select waybill_no package_code,
                        scan_time,
                        scan_type,
                        scan_site,
                        scan_site_code,
                        scan_user,
                        scan_user_code,
                        source,
                        good_type
                 from tab_bag_detail
                 where waybill_no like 'B%'
                   and rnk = 1
                   distribute by abs(hash(concat(scan_time,package_code)))%300
                   ),
     bag_waybill as (select waybill_no, package_code, max(bag) bag
                     from (select waybill_no,
                                  package_code,
                                  collect_set(if(apply_time is null or apply_time >= scan_time, package_code, null))
                                              over (partition by waybill_no,scan_site_code) bag
                           from (select a.waybill_no,
                                        a.scan_type,
                                        a.scan_site_code,
                                        a.scan_site,
                                        a.scan_time,
                                        a.package_code,
                                        refund_intercept.apply_time
                                 from tab_bag_detail a
                                          left join refund_intercept on a.waybill_no = refund_intercept.waybill_no
                                 where a.waybill_no like 'JT%') detail) flag
                     group by waybill_no, package_code),
     bag_trace as (
       select * from jms_tmp.tmp_scan_bagging_trace
     ),
     spilt_detail as (select *
                      from (select waybill_no,
                                   sacn_bag                                           on_package_code,
                                   send_station_number,
                                   send_station_network_code,
                                   send_station_network_type,
                                   first_code,
                                   second_code,
                                   third_code,
                                   package_code,
                                   send_station_send_packagecode,
                                   send_station_arrival_packagecode,
                                   next_code,
                                   next_code_number,
                                   send_station_bagging_lastest_scantime,
                                   send_station_unbagging_scantime,
                                   send_station_send_later_time,
                                   send_station_arrival_early_time,
                                   lag(send_station_network_code, 1, '')
                                       over (partition by waybill_no,sacn_bag order by coalesce(
                                               send_station_bagging_lastest_scantime, send_station_send_later_time,
                                               send_station_arrival_early_time) desc) lag_site_code,
                                   dt
                            from (select a.*,
                                         concat(nvl(package_code, ''), '-', nvl(send_station_send_packagecode, ''), '-',
                                                nvl(send_station_arrival_packagecode, ''), '-',
                                                nvl(send_station_unbagging_packagecode, '')) concat_package
                                  from sign_detail a
                                  where coalesce(package_code, send_station_send_packagecode,
                                                 send_station_arrival_packagecode,
                                                 send_station_unbagging_packagecode) is not null
                                    and coalesce(send_station_bagging_lastest_scantime, send_station_send_later_time,
                                                 send_station_arrival_early_time,
                                                 send_station_unbagging_scantime) is not null) detail lateral view EXPLODE(split(concat_package, '-')) scan_bag_detail as sacn_bag
                            where sacn_bag != '') detail),
     waybill_trace as (select detail.*
                       from (select sign_detail.*,
                                    nvl(bag_trace.package_code, '') bag_package_code,
                                    bag_trace.real_road,
                                    bag_trace.real_road_name
                             from (select *
                                   from spilt_detail
                                   where on_package_code != ''
                                     and send_station_network_code != lag_site_code) sign_detail
                                      left join bag_trace on sign_detail.on_package_code = bag_trace.package_code) detail),
     bag_trace_err as (select detail.waybill_no,
                              on_package_code,
                              waybill_real_road,
                              bag_package_code,
                              real_road
                       from (select *,
                                    regexp_replace(concat_ws('-', sort_array(list
                                        )
                                                       ),
                                                   '[0-9]{2} ', '') waybill_real_road
                             from (select *,
                                          collect_set(CONCAT(if(length(send_station_number) < 2,
                                                                concat('0', send_station_number),
                                                                send_station_number), ' ',
                                                             send_station_network_code))
                                                      over (partition by waybill_no,on_package_code)                                                                                                            list,
                                          next_code,
                                          row_number() over (partition by waybill_no,on_package_code order by send_station_number desc,nvl(send_station_send_later_time, send_station_arrival_early_time) desc) rn
                                   from waybill_trace
                                   where bag_package_code != '') detail
                             where rn = 1) detail
--                        where waybill_real_road != real_road
     )
        ,
     dim_first as (select first_code,collect_set(code_area) code_area_set,collect_set(network_code) network_code_set
                   from (select CODE first_code,destination_city_id city_id,coalesce(destination_area_id,destination_town_id) code_area,null network_code
                         from jms_dim.dim_lmdm_sys_first_code
                         where is_enable = 1
                           and is_delete = 1
                         union all
                         select express_center_short_code first_code, null destination_city_id, null code_area,express_org_code network_code
                         from jms_dim.dim_yl_sys_package_different_base) code_detail
                   group by first_code),
     dim_second as (select CODE second_code,collect_set(coalesce(area_id,town_id)) code_area_set
                    from jms_dim.dim_lmdm_sys_second_code
                    where is_enable = 1
                      and is_delete = 1
                    group by CODE),
     second_bag as (select package_number    package_code,
                           max(package_code) package_sys_code,
                           max(package_name) package_sys_name,
                           max(center_code)  center_code,
                           max(center_name)  center_name
                    from jms_dwd.dwd_second_package_list_base_dt
                    where dt >= date_add(date('{{ execution_date | cst_ds }}'), -90 + 1)
                      and dt <= '{{ execution_date | cst_ds }}'
                    group by package_number),
     first_bag as (select package_number    package_code,
                          max(package_code) package_sys_code,
                          max(package_name) package_sys_name,
                          max(center_code)  center_code,
                          max(center_name)  center_name
                   from jms_dwd.dwd_tab_electronic_package_list_base_dt
                   where dt >= date_add(date('{{ execution_date | cst_ds }}'), -90 + 1)
                     and dt <= '{{ execution_date | cst_ds }}'
                   group by package_number),
     mix_info as (
       select *,if(size(true_first_list)>0,1,0) is_mix_sys_true from(
        select min(audit_time) audit_time,mix_package_code,mix_package_name,apply_center_code,apply_center_name,dt,collect_set(detail.first_code) first_list,collect_set(dim_first.first_code) true_first_list from (
          select  audit_time,
                  mix_package_first_code,
                  apply_center_code,
                  apply_center_name,
                  first_code,
                  mix_package_code,
                  mix_package_name,
                  row_number() over(partition by mix_package_code,first_code,apply_center_code,dt order by audit_time desc) rn,
                  dt
          from  (
              select * from jms_dim.dim_yl_mix_package_config_dt
              where dt >= date_add(date('{{ execution_date | cst_ds }}'), -90 + 1)
              and dt <= '{{ execution_date | cst_ds }}'
              and audit_status = 1
          ) detail
          lateral view explode(split(mix_package_first_code,'/')) t as first_code
        ) detail left join
        dim_first on dim_first.first_code = detail.first_code
         where rn = 1
        group by mix_package_code,mix_package_name,apply_center_code,apply_center_name,dt
       ) detail
     ),
     bag_info as (select bag.*,
                         first.package_sys_code  first_code,
                         second.package_sys_code second_code,
                         first.package_sys_name  first_package_name,
                         first.center_code       first_center_code,
                         first.center_name       first_center_name,
                         second.package_sys_name second_package_name,
                         second.center_code      second_center_code,
                         second.center_name      second_center_name
                  from tab_bag bag
                           left join first_bag first
                                     on bag.package_code = first.package_code
                           left join second_bag second on bag.package_code = second.package_code),

     tab_info as (select detail.*
                  from (select *,
                               row_number() over (partition by waybill_no, package_code order by send_station_bagging_lastest_scantime desc ) rn,
                               max(is_start) over (partition by waybill_no, package_code)                                                     max_is_start
                        from sign_detail
                        where package_code is not null) detail
                  where detail.rn = 1)
        ,
     bag_error as (select /*+ BROADCAST(mix_info) */
                          bag_info.*,
                          dim_first.first_code   first_code_dim,
                          dim_second.second_code second_code_dim,
                          if(bag_info.good_type = '2', 0,
                             case
                                 when bag_info.first_code is not null then if(dim_first.first_code is not null, 0, 1)
                                --  when bag_info.mix_package_code is not null then if(bag_info.is_mix_sys_true = 1, 0, 1)
                                 when bag_info.second_code is not null then if(dim_second.second_code is not null, 0, 2)
                                 when (bag_info.first_code is null and bag_info.second_code is null) then 3
                                 else 0 end)     is_bag_err,
                         dim_first.code_area_set first_code_area_set,
                         dim_second.code_area_set second_code_area_set,
                         dim_first.network_code_set,
                         mix_info.first_list mix_first_list,
                         mix_info.mix_package_code
                   from bag_info
                            left join dim_first on bag_info.first_code = dim_first.first_code
                            left join dim_second on bag_info.second_code = dim_second.second_code
                            left join mix_info
                                     on mix_info.mix_package_code = bag_info.first_code
                                     and mix_info.audit_time  <= bag_info.scan_time
                                     and mix_info.dt = date(bag_info.scan_time)
                                     and mix_info.apply_center_code = bag_info.scan_site_code
                            )
                            ,
     tbl_difficult as (select waybill_no
                       from jms_dwd.dwd_tab_barscan_difficult_base_dt --操作平台-问题件表
                       where dt >= date_add(date('{{ execution_date | cst_ds }}'), -15 + 1)
                         and dt <= '{{ execution_date | cst_ds }}'
                         and problem_type_code in ('11a')
                       group by waybill_no),
     net as (select *
             from jms_dim.dim_network_whole_massage),
     err_detail as (select date(detail.dt)                      sign_date,
                           detail.bag_date,
                           detail.waybill_no,
                           detail.package_code,
                           case
                               when first_center_name is not null
                                   then first_center_name
                               when second_center_name is not null
                                   then second_center_name
                               end                              package_name,
                           nvl(bag_first_code, bag_second_code) bag_sys_code,
                           case
                               when bag_first_code is not null then first_code
                               when bag_second_code is not null then second_code
                               else nvl(first_code, second_code)
                               end                              waybill_sys_code,
                           scan_site_code                       network_code,
                           name                                 network_name,
                           network_type,
                           agent_id,
                           agent_code,
                           agent_name,
                           scan_user_code,
                           scan_user,
                           source,
                           is_start,
                           send_station_send_next_network_code,
                           next_station_network_code,
                           next_station_network_type,
                           next_name,
                           out_entrepot_code_new,
                           out_entrepot_name_new,
                           out_distribution_code_new,
                           out_distribution_name_new,
                           if(if(is_refund not in (1, 2), is_bag_tab_err = 1, is_return_tranfer_err = 1),
                              case
                                  when is_refund = 3 then 1
                                  when is_sys_code_err = 1 or is_sys_code_err = 2 then 2
                                  when is_auto_err = 1 then 3
                                  when is_pos_err = 1 then 4
--                                   when is_lmdm_first_err = 1 or is_lmdm_second_err = 1 or is_lmdm_null_err = 1 then 1
                                  else 4
                                  end, 0)                       err_bag_type,
                           if(if(is_refund not in (1, 2), is_bag_tab_err = 1, is_return_tranfer_err = 1),
                              case
                                  when is_refund = 3 then null --抬头错
                                  when is_sys_code_err = 1 or is_sys_code_err = 2 then null --运单三段码错
                                  when is_auto_err = 1 then '设备错建包'
                                  when is_pos_err = 1 then '操作错建包'
--                                   when is_lmdm_first_err = 1 or is_lmdm_second_err = 1 or is_lmdm_null_err = 1
--                                       then '包牌三段码错误'
                                  else '操作错建包'
                                  end, null)                    err_bag_type_name,
                           if(is_refund = 1, 1, 0)              is_reback,
                           if(is_refund = 2, 1, 0)              is_resend,
                           if(is_refund = 3, 1, 0)              is_head_err,
                           bag,
                           waybill_real_road,
                           real_road,
                           bag_trace_err_type,
                           bag_trace_err_name,
                           parent_network_code,
                           parent_network_name,
                           concat_ws(',',first_code_area_set) first_code_area_set,
                           concat_ws(',',second_code_area_set) second_code_area_set,
                           receiver_town_id,
                           receiver_township,
                           receiver_area_id,
                           receiver_area_name,
                           receiver_detailed_address,
                           is_adjust,
                           dispatch_network_code,
                           dispatch_network_name,
                           network_code_set,
                           is_diff_adjust,
                           dt
                    from (select  /*+ BROADCAST(tbl_difficult) */
                                 tab.waybill_no,
                                 tab.dt,
                                 date(tab.send_station_bagging_lastest_scantime) bag_date,
                                 tab.package_code,
                                 tab.first_code,
                                 tab.second_code,
                                 bag.first_code                                  bag_first_code,
                                 bag.second_code                                 bag_second_code,
                                 bag.scan_user_code,
                                 bag.scan_user,
                                 bag.source,
                                 bag.scan_site_code,
                                 bag.first_package_name,
                                 bag.first_center_code,
                                 bag.first_center_name,
                                 tab.next_station_network_code,
                                 tab.next_station_network_type,
                                 net1.name                                       next_name,
                                 net1.out_entrepot_code_new,
                                 net1.out_entrepot_name_new,
                                 net1.out_distribution_code_new,
                                 net1.out_distribution_name_new,
                                 max_is_start                                    is_start,
                                 tab.send_station_send_next_network_code,
                                 bag.second_package_name,
                                 bag.second_center_code,
                                 bag.second_center_name,
                                 net.name,
                                 net.agent_id,
                                 net.agent_code,
                                 net.agent_name,
                                 net.network_type,
                                 net.parent_network_code,
                                 net.parent_network_name,
                                 bag.first_code_dim,
                                 bag.second_code_dim,
                                 refund.apply_type_code,
                                 refund.apply_type_name,
                                 if(bag.is_bag_err = 1, 1, 0)                    is_lmdm_first_err,
                                 if(bag.is_bag_err = 2, 1, 0)                    is_lmdm_second_err,
                                 if(bag.is_bag_err = 3 or (bag.first_code is null and bag.second_code is null), 1,
                                    0)                                           is_lmdm_null_err,
                                 case
                                     when tab.first_code is null then 1
                                     when tab.second_code is null then 2
                                     else 0 end                                  is_sys_code_err,
                                 if(bag.source = '自动分拣设备',
                                    if(
                                            (tab.first_code = bag.first_code or tab.second_code = bag.second_code)
                                            or
                                            (bag.mix_package_code is not null and array_contains(bag.mix_first_list,tab.first_code))
                                            ,
                                            0, 1)
                                     , 0
                                     )                                           is_auto_err,
                                 if(bag.source = '移动端',
                                    if(
                                            (tab.first_code = bag.first_code or tab.second_code = bag.second_code)
                                            or
                                            (bag.mix_package_code is not null and array_contains(bag.mix_first_list,tab.first_code))
                                            ,
                                            0, 1)
                                     , 0
                                     )                                           is_pos_err,
                                 if(waybill_bag.good_type = '2', 0,
                                    case
                                        when bag.first_code is null and bag.second_code is null then 1
                                        when tab.first_code is null and tab.second_code is null then 1
                                        when tab.first_code = bag.first_code or tab.second_code = bag.second_code then 0
                                        when bag.mix_package_code is not null and array_contains(bag.mix_first_list,tab.first_code) then 0
                                        else 1 end)                              is_bag_tab_err,
                                 case
                                     when refund.apply_type_code = 4 then 1
                                     when refund.apply_type_code = 5 then 2
                                     when tbl_difficult.waybill_no is not null then 3
                                     else 0 end                                  is_refund,
                                 concat_ws('-', dup.bag)                         bag,
                                 waybill_real_road,
                                 real_road,
                                 case
                                     when size(dup.bag) > 1 then 1
                                     when trace.waybill_real_road != trace.real_road then 2
                                     else 0
                                     end                                         bag_trace_err_type,
                                 case
                                     when size(dup.bag) > 1 then '建包重复'
                                     when trace.waybill_real_road != trace.real_road then '包件不关联'
                                     else null
                                     end                                         bag_trace_err_name,
                                 case
                                     when refund.apply_type_code = 4 and
                                          (
                                              (
                                                  tab.return_sys_code_old_array[0] != bag.first_code and --老一段碼
                                                  (
                                                    if(bag.mix_package_code is not null , not array_contains(bag.mix_first_list,tab.return_sys_code_old_array[0]),1=1)    --混建包
                                                  )
                                              ) or
                                              tab.return_sys_code_old_array[1] != bag.second_code  --老二段碼
                                          )
                                           and
                                          (
                                              (   tab.return_sys_code_new_array[0] != bag.first_code and --新一段碼
                                                  (
                                                    if(bag.mix_package_code is not null , not array_contains(bag.mix_first_list,tab.return_sys_code_new_array[0]),1=1)   --混建包
                                                  )
                                              )  or
                                              tab.return_sys_code_new_array[1] != bag.second_code   --新二段碼
                                          )
                                          and date(return_apply_time) >= date_add(date('{{ execution_date | cst_ds }}'), -15 + 1)
                                         then 1
                                     when refund.apply_type_code = 5 and
                                          (
                                              (   tab.transfer_sys_code_old_array[0] != bag.first_code and
                                                  (
                                                    if(bag.mix_package_code is not null , not array_contains(bag.mix_first_list,tab.transfer_sys_code_old_array[0]),1=1)   --混建包
                                                  )
                                              )
                                           or
                                           tab.transfer_sys_code_old_array[1] != bag.second_code
                                          )
                                          and
                                          (   (
                                                  tab.transfer_sys_code_new_array[0] != bag.first_code and
                                                  (
                                                    if(bag.mix_package_code is not null , not array_contains(bag.mix_first_list,tab.transfer_sys_code_new_array[0]),1=1)   --混建包
                                                  )
                                              )
                                           or
                                           tab.transfer_sys_code_new_array[1] != bag.second_code
                                          ) and date(transfer_apply_time) >= date_add(date('{{ execution_date | cst_ds }}'), -15 + 1)
                                         then 1
                                     else
                                         0
                                     end                                         is_return_tranfer_err,
                                    bag.first_code_area_set,
                                    bag.second_code_area_set,
                                    null receiver_town_id,
                                    waybill.receiver_township,
                                    waybill.receiver_area_id,
                                    waybill.receiver_area_name,
                                    waybill.receiver_detailed_address,
                                    case when array_contains(bag.first_code_area_set,waybill.receiver_area_id) then 1
                                         when array_contains(bag.second_code_area_set,waybill.receiver_area_id) then 1
                                    else 0 end is_adjust,
                                    waybill.dispatch_network_code,
                                    waybill.dispatch_network_name,
                                    bag.network_code_set,
                                    if(array_contains(bag.network_code_set,waybill.dispatch_network_code),1,0) is_diff_adjust
                          from tab_info tab
                                   left join (
                                              select
                                                a.waybill_no,
                                                a.receiver_township,
                                                a.receiver_area_id,
                                                a.receiver_city_name,
                                                a.receiver_area_name,
                                                a.receiver_detailed_address,
                                                a.dispatch_network_code,
                                                a.dispatch_network_name
                                              from
                                                jms_dwd.dwd_yl_oms_oms_waybill_incre_dt a
                                              where
                                                a.dt between date_add('{{ execution_date | cst_ds }}', -10) and  '{{ execution_date | cst_ds }}'
                                   ) waybill
                                             on tab.waybill_no = nvl(waybill.waybill_no,'')
                                   left join bag_error bag
                                             on tab.package_code = bag.package_code
                                   left join bag_waybill dup on dup.waybill_no = tab.waybill_no and
                                                                dup.package_code = tab.package_code
                                   left join bag_trace_err trace on trace.waybill_no = tab.waybill_no and
                                                                    trace.on_package_code = tab.package_code
                                   left join refund_dup refund on tab.waybill_no = refund.waybill_no
                                   left join net on net.code = bag.scan_site_code
                                   left join tbl_difficult on tab.waybill_no = tbl_difficult.waybill_no
                                   left join tab_bag_detail waybill_bag on waybill_bag.waybill_no = tab.waybill_no
                                   and waybill_bag.package_code = tab.package_code and waybill_bag.pack_rnk = 1
                                   left join net net1 on next_station_network_code = net1.code
                                   ) detail
)


insert
overwrite
table
jms_dm.dm_scan_bagging_true_tmp_detail_dt partition(dt)
select *
from err_detail distribute by dt,abs(hash(concat(waybill_no,package_code)))%100;


insert
    overwrite
    table jms_dm.dm_scan_bagging_true_detail_dt
    partition
(dt
    ,
 dtype)
select *
from (select package_code,
             agent_code,
             agent_name,
             waybill_no,
             bag_date,
             package_name,
             bag_sys_code,
             waybill_sys_code,
             network_code,
             network_name,
             network_type,
             scan_user_code,
             scan_user,
             err_bag_type,
             err_bag_type_name,
             is_reback,
             is_resend,
             is_head_err,
             is_bag_dup,
             is_trace_err,
             is_start,
             bag_list,
             source,
             sign_date,
             parent_network_code,
             parent_network_name,
             is_adjust,
             dt,
             case
                 when err_bag_type > 0 or bag_trace_err_type > 0 or is_adjust > 0
                     then 'error'
                 else 'true' end dtype
      from (select sign_date,
                   bag_date,
                   waybill_no,
                   package_code,
                   package_name,
                   bag_sys_code,
                   waybill_sys_code,
                   network_code,
                   network_name,
                   network_type,
                   is_start,
                   next_station_network_code,
                   next_station_network_type,
                   next_name,
                   out_entrepot_code_new,
                   out_entrepot_name_new,
                   out_distribution_code_new,
                   out_distribution_name_new,
                   agent_code,
                   agent_name,
                   agent_id,
                   scan_user_code,
                   scan_user,
                   source,
                   if(err_bag_type > 2 and (is_adjust + is_diff_adjust)  >= 1, 0 ,err_bag_type)                     err_bag_type,
                   if(err_bag_type > 2 and (is_adjust + is_diff_adjust)  >= 1, null ,err_bag_type_name)                 err_bag_type_name,
                   is_reback,
                   is_resend,
                   is_head_err,
                   concat_ws('-', bag) as           bag_list,
                   waybill_real_road,
                   real_road,
                   bag_trace_err_type,
                   bag_trace_err_name,
                   if(bag_trace_err_type = 1, 1, 0) is_bag_dup,
                   if(bag_trace_err_type = 2, 1, 0) is_trace_err,
                   parent_network_code,
                   parent_network_name,
                   if((is_adjust + is_diff_adjust)  >= 1 and err_bag_type > 2, 1 ,0) is_adjust,
                   dt
            from jms_dm.dm_scan_bagging_true_tmp_detail_dt
            where dt = '{{ execution_date | cst_ds }}') detail) type_detail
    distribute by dt
   , dtype
   , abs(hash(waybill_no))%80;